USE CourierDatabase
GO
--Add Employee
CREATE PROCEDURE AddEmp
@empId INT,
@empName VARCHAR(25),
@empAddress VARCHAR(40),
@empPhone INT,
@empMail VARCHAR(40),
@empImage VARCHAR(100),
@status INT,
@decentralization INT,
@loginName VARCHAR(15) ,
@loginPassword VARCHAR(15),
@question INT,
@answer VARCHAR(40),
@timeCreated DATE
AS
BEGIN	
	INSERT INTO CourierDatabase.dbo.Employee(Emp_Id,Emp_Name,Emp_Address,Emp_Phone,Emp_Mail,Emp_Image,Status_Id,Decentralization_Id,Login_Name,Login_Password,S_Question,S_Answer,Time_Created) VALUES(@empId,@empName,@empAddress,@empPhone,@empMail,@empImage,@status,@decentralization,@loginName,@loginPassword,@question,@answer,@timeCreated)	
END
GO

--Find Employee
CREATE PROCEDURE FindEmp
@loginName VARCHAR(15),
@Found INT OUTPUT
As
BEGIN
	DECLARE @checkLogin INT
	SET @checkLogin = (SELECT COUNT(*) FROM CourierDatabase.dbo.Employee
	WHERE Login_Name = @loginName)
	IF(@checkLogin != 0)
	BEGIN
		SET @Found = 1
	END
	ELSE
		SET @Found = 0
END
GO

--Delete Employee
CREATE PROCEDURE DelEmp
@loginName VARCHAR(15)
AS
BEGIN	
	DELETE FROM CourierDatabase.dbo.Attendance WHERE Login_Name=@loginName
	DELETE FROM CourierDatabase.dbo.Employee WHERE Login_Name=@loginName
END
GO

--Show info of Employee
CREATE PROCEDURE ShowEmp
@loginName VARCHAR(15)
AS
BEGIN	
	SELECT * FROM CourierDatabase.dbo.Employee INNER JOIN CourierDatabase.dbo.Emp_Decentralization 
	ON CourierDatabase.dbo.Employee.Decentralization_Id=CourierDatabase.dbo.Emp_Decentralization.Decentralization_Id
	INNER JOIN CourierDatabase.dbo.Working_Status ON CourierDatabase.dbo.Employee.Status_Id = CourierDatabase.dbo.Working_Status.Status_Id
	WHERE CourierDatabase.dbo.Employee.Login_Name=@loginName					
END
GO

--Show Employee except him/herselft
CREATE PROCEDURE List_Emp
@option INT,
@loginName VARCHAR(15),
@myloginName VARCHAR(15),
@decentralization INT,
@status INT
AS
BEGIN
	IF (@option = 0)
	BEGIN
		SELECT Emp_Id,Login_Name,Emp_Name,Emp_Decentralization.Decentralization_Name,Working_Status.Status_Content FROM CourierDatabase.dbo.Employee INNER JOIN CourierDatabase.dbo.Emp_Decentralization 
		ON CourierDatabase.dbo.Employee.Decentralization_Id=CourierDatabase.dbo.Emp_Decentralization.Decentralization_Id
		INNER JOIN CourierDatabase.dbo.Working_Status ON CourierDatabase.dbo.Employee.Status_Id = CourierDatabase.dbo.Working_Status.Status_Id
		WHERE CourierDatabase.dbo.Employee.Login_Name = @loginName
		
	END

	ELSE IF (@option = 1)
	BEGIN
		SELECT Emp_Id,Login_Name,Emp_Name,Emp_Decentralization.Decentralization_Name,Working_Status.Status_Content FROM CourierDatabase.dbo.Employee INNER JOIN CourierDatabase.dbo.Emp_Decentralization 
		ON CourierDatabase.dbo.Employee.Decentralization_Id=CourierDatabase.dbo.Emp_Decentralization.Decentralization_Id
		INNER JOIN CourierDatabase.dbo.Working_Status ON CourierDatabase.dbo.Employee.Status_Id = CourierDatabase.dbo.Working_Status.Status_Id
		WHERE CourierDatabase.dbo.Employee.Login_Name != @myloginName 
		AND CourierDatabase.dbo.Employee.Status_Id != 3
	END
	
	ELSE IF (@option = 2)
	BEGIN
		SELECT Emp_Id,Login_Name,Emp_Name,Emp_Decentralization.Decentralization_Name,Working_Status.Status_Content FROM CourierDatabase.dbo.Employee INNER JOIN CourierDatabase.dbo.Emp_Decentralization 
		ON CourierDatabase.dbo.Employee.Decentralization_Id=CourierDatabase.dbo.Emp_Decentralization.Decentralization_Id
		INNER JOIN CourierDatabase.dbo.Working_Status ON CourierDatabase.dbo.Employee.Status_Id = CourierDatabase.dbo.Working_Status.Status_Id
		WHERE CourierDatabase.dbo.Employee.Login_Name != @myloginName 
	END
	
	ELSE IF(@option = 3)
	BEGIN
		SELECT Emp_Id,Login_Name,Emp_Name,Emp_Decentralization.Decentralization_Name,Working_Status.Status_Content FROM CourierDatabase.dbo.Employee INNER JOIN CourierDatabase.dbo.Emp_Decentralization 
		ON CourierDatabase.dbo.Employee.Decentralization_Id=CourierDatabase.dbo.Emp_Decentralization.Decentralization_Id
		INNER JOIN CourierDatabase.dbo.Working_Status ON CourierDatabase.dbo.Employee.Status_Id = CourierDatabase.dbo.Working_Status.Status_Id
		WHERE CourierDatabase.dbo.Employee.Login_Name != @myloginName
		AND CourierDatabase.dbo.Employee.Status_Id = @status
	END
	
	ELSE
	BEGIN
		SELECT Emp_Id,Login_Name,Emp_Name,Emp_Decentralization.Decentralization_Name,Working_Status.Status_Content FROM CourierDatabase.dbo.Employee INNER JOIN CourierDatabase.dbo.Emp_Decentralization 
		ON CourierDatabase.dbo.Employee.Decentralization_Id=CourierDatabase.dbo.Emp_Decentralization.Decentralization_Id
		INNER JOIN CourierDatabase.dbo.Working_Status ON CourierDatabase.dbo.Employee.Status_Id = CourierDatabase.dbo.Working_Status.Status_Id
		WHERE CourierDatabase.dbo.Employee.Login_Name != @myloginName
		AND CourierDatabase.dbo.Employee.Decentralization_Id = @decentralization
		AND CourierDatabase.dbo.Employee.Status_Id = @status		
	END
END
GO

--Show detail about a specific employee
CREATE PROCEDURE ShowDetail
@loginName VARCHAR(15)
AS
BEGIN	
	SELECT Emp_Id,Login_Name,Time_Created,Emp_Name,Emp_Address,Emp_Phone,Emp_Mail,Emp_Decentralization.Decentralization_Name,Working_Status.Status_Content,Emp_Image FROM CourierDatabase.dbo.Employee INNER JOIN CourierDatabase.dbo.Emp_Decentralization 
	ON CourierDatabase.dbo.Employee.Decentralization_Id=CourierDatabase.dbo.Emp_Decentralization.Decentralization_Id
	INNER JOIN CourierDatabase.dbo.Working_Status ON CourierDatabase.dbo.Employee.Status_Id = CourierDatabase.dbo.Working_Status.Status_Id
	WHERE CourierDatabase.dbo.Employee.Login_Name=@loginName					
END
GO

--Edit info of a chosen employee
CREATE PROCEDURE UpdateInfo
@loginname VARCHAR(15),
@name VARCHAR(25),
@address VARCHAR(40),
@phone INT,
@mail VARCHAR(40),
@image VARCHAR(50),
@decentralization INT,
@workingstatus INT
AS
BEGIN
	UPDATE CourierDatabase.dbo.Employee 
	SET CourierDatabase.dbo.Employee.Emp_Name = @name,
		 CourierDatabase.dbo.Employee.Emp_Address = @address,
		 CourierDatabase.dbo.Employee.Emp_Phone = @phone,
		 CourierDatabase.dbo.Employee.Emp_Mail = @mail,
		 CourierDatabase.dbo.Employee.Emp_Image = @image,
		 CourierDatabase.dbo.Employee.Decentralization_Id = @decentralization,
		 CourierDatabase.dbo.Employee.Status_Id = @workingstatus
	WHERE Login_Name = @loginname
END
GO

--new list Attendance for new day
CREATE PROCEDURE setAttendanceTable
@loginName VARCHAR(50),
@dateAbsence DATE,
@untilDay DATE,
@note VARCHAR(150)
AS
BEGIN
	DECLARE @beforeStatus INT
	DECLARE @checkDate DATE
	DECLARE @tempStatus INT
	SET @checkDate = (SELECT MAX(Until_Day) FROM Attendance WHERE Login_Name = @loginName)
	SET @beforeStatus = (SELECT Status_Id FROM Employee WHERE @loginName = Login_Name)
	IF(@beforeStatus != 3) 
	BEGIN
		IF(@checkDate != @dateAbsence)
		BEGIN
			IF(@beforeStatus != 2)
			BEGIN
				INSERT INTO CourierDatabase.dbo.Attendance
				VALUES (@loginName,1,@dateAbsence,@checkDate,@note,@beforeStatus)
				UPDATE Employee SET Status_Id = 2 WHERE @loginName = Login_Name
			END
		END
		ELSE
		BEGIN
			IF(@beforeStatus != 2)
			BEGIN
				INSERT INTO CourierDatabase.dbo.Attendance
				VALUES (@loginName,0,@dateAbsence,@untilDay,@note,@beforeStatus)
				UPDATE Employee SET Status_Id = @beforeStatus WHERE @loginName = Login_Name
			END
			ELSE
			BEGIN
				DECLARE @tempDate DATE
				SET @tempDate = (SELECT REPLACE(CONVERT(VARCHAR, DATEADD(dd, -1, GETDATE()), 102), '.', '-'))
				SET @tempStatus = (SELECT Before_Status FROM Attendance WHERE @loginName = Login_Name AND @tempDate = Absence_Date)
				INSERT INTO CourierDatabase.dbo.Attendance
				VALUES (@loginName,0,@dateAbsence,@untilDay,@note,@tempStatus)
				UPDATE Employee SET Status_Id = @tempStatus WHERE @loginName = Login_Name
			END
		END
	END
END
GO

CREATE PROCEDURE updateAttendance
@loginName VARCHAR(15),
@currentDate DATE,
@absence INT,
@dateAttendance DATE,
@note VARCHAR(150)
AS
BEGIN
	UPDATE Attendance
	SET Absence = @absence, Until_Day = @dateAttendance, Note = @note
	WHERE @loginName = Login_Name AND @currentDate = Absence_Date
END
GO

--find employee in attendance table
CREATE PROCEDURE findAttendance
@loginName VARCHAR(50),
@function INT,
@dateAttendance DATE,
@absence INT
AS
BEGIN
	DECLARE @Temp TABLE (
		Login_Name VARCHAR(50),
		Decentralization_Id INT,
		Absence INT,
		Until_Day DATE,
		Note VARCHAR(500),
		Absence_Date DATE
	)  
	DECLARE @TempResult TABLE (
		Login_Name VARCHAR(50),
		Decentralization_Id INT,
		Absence INT,
		Until_Day DATE,
		Note VARCHAR(500),
		Absence_Date DATE
	)
	INSERT INTO @Temp SELECT Attendance.Login_Name,Emp_Decentralization.Decentralization_Id,Absence,Until_Day,Note,Attendance.Absence_Date FROM Employee,Attendance,Emp_Decentralization
	WHERE Attendance.Login_Name = Employee.Login_Name AND Emp_Decentralization.Decentralization_Id = Employee.Decentralization_Id
	IF(@loginName != '')
	BEGIN
		INSERT INTO @TempResult SELECT * FROM @Temp WHERE Login_Name LIKE '%' + @loginName + '%'
		DELETE FROM @Temp
		INSERT INTO @Temp SELECT * FROM @TempResult
		DELETE FROM @TempResult
	END
	IF(@function != 5)
	BEGIN
		INSERT INTO @TempResult SELECT * FROM @Temp WHERE Decentralization_Id = @function
		DELETE FROM @Temp
		INSERT INTO @Temp SELECT * FROM @TempResult
		DELETE FROM @TempResult
	END
	IF(@dateAttendance != '')
	BEGIN
		INSERT INTO @TempResult SELECT * FROM @Temp WHERE Absence_Date = @dateAttendance
		DELETE FROM @Temp
		INSERT INTO @Temp SELECT * FROM @TempResult
		DELETE FROM @TempResult
	END
	IF(@absence != -1)
	BEGIN
		INSERT INTO @TempResult SELECT * FROM @Temp WHERE Absence = @absence
		DELETE FROM @Temp
		INSERT INTO @Temp SELECT * FROM @TempResult
		DELETE FROM @TempResult
	END
	SELECT * INTO ##ResultAttendanceTable FROM @Temp
END
GO

--delete employee in attendance table
CREATE PROCEDURE deleteAttendance
@loginName VARCHAR(15),
@currentDate DATE
AS
BEGIN
	DELETE FROM Attendance WHERE Login_Name = @loginName AND Absence_Date = @currentDate
END
GO

--check employee had in database
CREATE PROCEDURE checkIntoDatabase
@loginName VARCHAR(15),
@currentDate DATE,
@checkOut INT OUTPUT
AS
BEGIN
	SET @checkOut = (SELECT COUNT(*) FROM Attendance WHERE Login_Name = @loginName AND Absence_Date = @currentDate)
END